/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.eng.scc.core.db.manager.service;

import com.eng.scc.core.conn.serial.ProtocolData;
import com.eng.scc.core.data.Address;
import com.eng.scc.core.data.City;
import com.eng.scc.core.data.Company;
import com.eng.scc.core.data.Device;
import com.eng.scc.core.data.DeviceInfo;
import com.eng.scc.core.data.Driver;
import com.eng.scc.core.data.HomeState;
import com.eng.scc.core.data.People;
import com.eng.scc.core.data.Vehicle;
import com.eng.scc.core.db.Database;
import com.eng.scc.core.schema.Schema.*;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;

/**
 *
 * @author Rafael
 */
public class FuelControllerManagerService
{
    private static FuelControllerManagerService managerService = null;
    
    public static FuelControllerManagerService getInstance()
    {
        if ( managerService == null )
        {
            managerService = new FuelControllerManagerService();
        }
        
        return managerService;
    }

    private FuelControllerManagerService(){}
    
    public void addAddress( Address address ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.insert( address, Addresses.fetcher, Addresses.INSERT );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void updateAdress( Address address ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.update( address, Addresses.fetcher, Addresses.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void addCity( City city ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.insert( city, Cities.fetcher, Cities.INSERT );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void updateCity( City city ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.update( city, Cities.fetcher, Cities.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void addHomeState( HomeState homestate ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.insert( homestate, HomeStates.fetcher, HomeStates.INSERT );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void updateHomeState( HomeState homestate ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.update( homestate, HomeStates.fetcher, HomeStates.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public HomeState getHomeState( int i ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            String sql = HomeStates.SELECT + " where " + HomeStates.Fields.ID + " = " + i;
            
            return db.fechOne( sql, HomeStates.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public HomeState getHomeState( String i ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            String sql = HomeStates.SELECT + " where " + HomeStates.Fields.NAME + " = " + db.quote( i );
            
            return db.fechOne( sql, HomeStates.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public List<HomeState> getHomeStates() throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            String sql = HomeStates.SELECT + " order by " + HomeStates.Fields.NAME;
            
            return db.fechMany( sql, HomeStates.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public List<City> getCities( int i ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            String sql = Cities.SELECT + 
                         " where " + 
                         Cities.Fields.REF_HOME_STATE  + " = " + i + 
                         " order by " + Cities.Fields.NAME;
            
            return db.fechMany( sql, Cities.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public void addCompany( Company c ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.insert( c, Companies.fetcher, Companies.INSERT );
        }
        
        finally
        {
            db.release();
        }
    }

    public City getCity( int cityId ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            String sql = Cities.SELECT + " where " + Cities.Fields.ID + " = " + cityId;
            
            return db.fechOne( sql, Cities.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public List<Company> getCompanies() throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            return db.fechMany( Companies.SELECT + " where " + Companies.Fields.STATE + " = " + Company.ACTIVE, Companies.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public Address getAddress( int id ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            return db.fechOne( Addresses.SELECT + " where " + Addresses.Fields.ID + " = " + id, Addresses.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public void updateAddress( Address address ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.update( address, Addresses.fetcher, Addresses.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void updateCompany( Company c ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.update( c, Companies.fetcher, Companies.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }

    public void deleteCompany( Company c ) throws Exception
    {
        c.setState( Company.INACTIVE );
        updateCompany( c );
    }
    
    public void deleteDriver( Driver d ) throws Exception
    {
        d.setState( Company.INACTIVE );
        updateDriver( d );
    }

    public People getPeople( int peopleId ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            String sql = Peoples.SELECT + " where " + Peoples.Fields.ID + " = " + peopleId;
            
            return db.fechOne( sql, Peoples.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public void addPeople( People people ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.insert( people, Peoples.fetcher, Peoples.INSERT );
        }
        
        finally
        {
            db.release();
        }
    }

    public void updatePeople( People people ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.update( people, Peoples.fetcher, Peoples.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public Company getCompany( int id ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            String sql = Companies.SELECT + " where " + Companies.Fields.ID + " = " + id;
            
            return db.fechOne( sql, Companies.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public Driver getDriver( int id ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            String sql = Drivers.SELECT + " where " + Drivers.Fields.ID + " = " + id;
            
            return db.fechOne( sql, Drivers.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public void addDriver( Driver d ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.insert( d, Drivers.fetcher, Drivers.INSERT );
        }
        
        finally
        {
            db.release();
        }
    }

    public List<Driver> getDrivers() throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            return db.fechMany( Drivers.SELECT + " where " + Drivers.Fields.STATE + " = " + Driver.ACTIVE, Drivers.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public List<Vehicle> getVehicles() throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            return db.fechMany( Vehicles.SELECT + " where " + Vehicles.Fields.STATE + " = " + Vehicle.ACTIVE, Vehicles.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public void updateDriver( Driver d ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.update(d, Drivers.fetcher, Drivers.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }

    public void addVehicle( Vehicle vehicle ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.insert( vehicle, Vehicles.fetcher, Vehicles.INSERT );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void updateVehicle( Vehicle vehicle ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            db.update( vehicle, Vehicles.fetcher, Vehicles.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }

    public List<Vehicle> getVehicles( Company company ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            return db.fechMany( Vehicles.SELECT + " where " + Vehicles.Fields.STATE + " = " + Vehicle.ACTIVE + " and " + Vehicles.Fields.REF_COMPANY + " = " + company.getId(), Vehicles.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public void addDeviceInfos( ProtocolData protocolData ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {
            Device d = getDeviceBySerial( protocolData.getSerial() );
            
            if ( d == null )
            {
                throw new IllegalArgumentException( "Device serial: " + protocolData.getSerial() + " not exists in Database! Must be added first!" );
            }
            
            for ( ProtocolData.FuelValue fuelValue : protocolData.getValues() )
            {
                short i = 0; 
                
                for ( Short s : fuelValue.getData() )
                {
                    DeviceInfo deviceInfo = new DeviceInfo();
                    
                    deviceInfo.setDeviceId( d.getId() );
                    
                    Calendar c = Calendar.getInstance();
                    c.setTimeInMillis( fuelValue.getTransmitedDate().getTime() );
                    
                    c.add( Calendar.MINUTE, i++ );
                    
                    deviceInfo.setTransmitionDate( new Date( c.getTimeInMillis() ) );
                    deviceInfo.setInfo( s );
                
                    db.insert( deviceInfo, DeviceInfos.fetcher, DeviceInfos.INSERT );
                }
            }
        }
        
        finally
        {
            db.release();
        }
    }

    public Device getDeviceBySerial( int serial ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {   
            return db.fechOne( Devices.SELECT + 
                               " WHERE " + 
                               Devices.Fields.SERIAL + " = " + serial , Devices.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void addDevice( Device d ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {   
            db.insert( d, Devices.fetcher, Devices.INSERT );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void updateDevice( Device d ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {   
            db.update( d, Devices.fetcher, Devices.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public void deleteDevice( Device d ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {   
            d.setStatus( 1 );
            db.update( d, Devices.fetcher, Devices.UPDATE );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public List<Device> getDevices() throws Exception
    {
        return getDevices( 0 );
    }

    public List<Device> getDevices( int id ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {   
            return db.fechMany( Devices.SELECT + " WHERE " + Devices.Fields.STATUS + " = 0" + (id > 0 ? (" and " + Devices.Fields.REF_VEHICLE + " = " + id) : "" ), Devices.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public List<DeviceInfo> getDeviceInfos( int deviceId ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {   
            return db.fechMany( DeviceInfos.SELECT + " WHERE " + DeviceInfos.Fields.REF_DEVICE + " = " + deviceId, 
                                DeviceInfos.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }
    
    public Device getDevice( int vehicleId ) throws Exception
    {
        Database db = Database.getInstance();
        
        try
        {   
            return db.fechOne( Devices.SELECT + " WHERE " + Devices.Fields.REF_VEHICLE + " = " + vehicleId, 
                                Devices.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public List<DeviceInfo> getDeviceInfos( Timestamp dateStart, Timestamp dateEnd, int deviceId ) throws Exception
    {
        DeviceInfos DI = DeviceInfos.table;
        
        Database db = Database.getInstance();
        
        try
        {
            String sql = DI.SELECT + 
                         " WHERE " + 
                         DeviceInfos.Fields.REF_DEVICE + " = " + deviceId + 
                         " AND " +
                         DeviceInfos.Fields.DT_TRANSMITION + 
                         " BETWEEN " +
                         db.quote( dateStart ) +
                         " AND " + 
                         db.quote( dateEnd );
            
                         
            
            return db.fechMany( sql, DeviceInfos.fetcher );
        }
        
        finally
        {
            db.release();
        }
    }

    public void deleteVehicle( Vehicle vehicle ) throws Exception
    {
        vehicle.setState( Vehicle.INACTIVE );
        updateVehicle( vehicle );
    }
}
